package models

import (
	"context"
	"database/sql"
	"encoding/json"
	"errors"
	"sort"
	"time"

	"github.com/jmoiron/sqlx"
	"github.com/lib/pq"
	"gopkg.in/guregu/null.v4"
)

type CourseModel struct {
	DB *sqlx.DB
}

type CourseSubCategory struct {
	ID    int64  `json:"id"`
	Title string `json:"title"`
	Icon  string `json:"icon"`
}

type CourseCategory struct {
	ID     int64             `json:"id"`
	Title  string            `json:"title"`
	Icon   string            `json:"icon"`
	Subs []CourseSubCategory `json:"subs"`
}

// 获取所有课程类别
func (m *CourseModel) GetAllCategories() ([]CourseCategory, error) {
	query := `
		SELECT
			c.id, c.title, c.icon,
			array_agg(s.id) AS subs_ids,
			array_agg(s.title) AS subs_titles,
			array_agg(s.icon) AS subs_icons
		FROM 
			course.categories c
		LEFT JOIN 
			course.categories s ON c.id = s.parent_id
		WHERE 
			c.parent_id IS NULL
		GROUP BY
			c.id, c.title
		ORDER BY 
			c.id`

	ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)
	defer cancel()

	rows, err := m.DB.QueryContext(ctx, query)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	categories := []CourseCategory{}
	for rows.Next() {
		var category CourseCategory
		var subIDs []int64
		var subTitles []string
		var subIcons  []string

		err := rows.Scan(&category.ID, &category.Title, &category.Icon, 
			pq.Array(&subIDs), pq.Array(&subTitles), pq.Array(&subIcons))
		if err != nil {
			return nil, err
		}

		if len(subIDs) != len(subTitles) || len(subIDs) != len(subIcons) {
			return nil, errors.New("无效的子类别数据")
		}

		for i := 0; i < len(subIDs); i++ {
			category.Subs = append(category.Subs, CourseSubCategory{
				ID:    subIDs[i],
				Title: subTitles[i],
				Icon:  subIcons[i],
			})
		}
		categories = append(categories, category)
	}

	if err = rows.Err(); err != nil {
		return nil, err
	}

	return categories, nil
}

// 获取热门课程
func (m *CourseModel) GetTopCourseKeys() ([]string, error) {
	return []string{
		"Vue", "Flutter", "Golang", "Unreal",
		"ChatGPT", "C/C++", "Python", "Java",
		"Swift", "Blender", "Android",
	}, nil
}

// 获取课程列表
type CourseBrief struct {
	Total        int     `json:"-" db:"total"`
	ID           int64   `json:"id" db:"id"`
	Title        string  `json:"title" db:"title"`
	Image        string  `json:"image" db:"image"`
	VideoCount   int64   `json:"video_count" db:"video_count"`
	Duration     int64   `json:"duration" db:"duration"`
	Level        string  `json:"level" db:"level"`
	Price        float32 `json:"price" db:"price"`
	Author       string  `json:"author" db:"author"`
	AuthorID     int64   `json:"author_id" db:"author_id"`
	StudentCount int64   `json:"student_count" db:"student_count"`
}

// 获取课程列表
func (m *CourseModel) GetCourseList(categoryId int, filter *PageFilter) (
	[]*CourseBrief, *PageMeta, error) {
	var query string
	column := filter.sortColumn()
	var where = ""
	if categoryId != 0 {
		where = "WHERE id = :categoryId"
	}
	if column != nil {
		query = `
			WITH RECURSIVE category_tree AS (
				SELECT id, parent_id
				FROM course.categories
				` + where + `
				UNION ALL
				SELECT c.id, c.parent_id
				FROM course.categories c
				INNER JOIN category_tree ct ON c.parent_id = ct.id
			)
			SELECT
				COUNT(1) OVER() as total, c.id as id, c.title as title, c.image as image,
				COUNT(v.id) AS video_count, SUM(v.duration) as duration, c.level as level,
				c.price as price, u.name as author, u.id as author_id,
				(
					SELECT COUNT(user_id) FROM business.orders o WHERE o.course_id = c.id
				) AS student_count
			FROM course.courses c
			LEFT JOIN course.table_contents tc ON c.id = tc.course_id
			LEFT JOIN course.videos v ON tc.id = v.parent_id
			INNER JOIN appuser.users u ON c.owner_id = u.id
			WHERE c.category_id IN (SELECT id FROM category_tree)
			GROUP BY c.id, u.id, u.name
			ORDER BY ` + *column + ` ` + filter.sortDirection() + `, id DESC
			LIMIT :length OFFSET :start`
	} else {
		query = `
			WITH RECURSIVE 
				category_tree AS (
					SELECT id, parent_id
					FROM course.categories
					` + where + `
					UNION ALL
					SELECT c.id, c.parent_id
					FROM course.categories c
					INNER JOIN category_tree ct ON c.parent_id = ct.id
				)
			SELECT
				COUNT(1) OVER() as total, c.id as id, c.title as title, c.image as image,
				COUNT(v.id) AS video_count, SUM(v.duration) as duration, c.level as level,
				c.price as price, u.name as author, u.id as author_id,
				(
					SELECT COUNT(user_id) FROM business.orders o WHERE o.course_id = c.id
				) AS student_count
			FROM 
				course.courses c
			LEFT JOIN 
				course.table_contents tc 
			ON 
				c.id = tc.course_id
			LEFT JOIN 
				course.videos v 
			ON 
				tc.id = v.parent_id
			INNER JOIN 
				appuser.users u 
			ON 
				c.owner_id = u.id
			WHERE 
				c.category_id 
			IN 
				(SELECT id FROM category_tree)
			GROUP BY 
				c.id, u.id, u.name
			ORDER BY 
				id DESC
			LIMIT 
				:length OFFSET :start`
	}

	ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)
	defer cancel()

	args := map[string]interface{}{
		"length": filter.limit(),
		"start":  filter.start(),
	}

	if categoryId != 0 {
		args["categoryId"] = categoryId
	}

	rows, err := m.DB.NamedQueryContext(ctx, query, args)
	if err != nil {
		return nil, nil, err
	}
	defer rows.Close();

	// 扫描结果
	courses := []*CourseBrief{}
	for rows.Next() { 
		var course CourseBrief
		err := rows.StructScan(&course)
		if err != nil {
			return nil, nil, err
		}
		courses = append(courses, &course)
	}
	if err = rows.Err(); err != nil {
		return nil, nil, err
	}

	totalRecords := 0
	if len(courses) > 0 {
		totalRecords = courses[0].Total
	}
	pageMeta := filter.calculateMetadata(totalRecords)
	return courses, pageMeta, nil
}

// 课程查询
func (m *CourseModel) SearchCourse(key string, filter *PageFilter) (
	[]*CourseBrief, *PageMeta, error) {
		query := `
			SELECT
				COUNT(1) OVER() as total, c.id as id, c.title as title, c.image as image,
				COUNT(v.id) AS video_count, SUM(v.duration) as duration, c.level as level,
				c.price as price, u.name as author,  u.id as author_id,
				(
					SELECT COUNT(user_id) FROM business.orders o WHERE o.course_id = c.id
				) AS student_count
			FROM 
				course.courses c
			LEFT JOIN 
				course.table_contents tc 
			ON
				c.id = tc.course_id
			LEFT JOIN 
				course.videos v
			ON 
				tc.id = v.parent_id
			INNER JOIN 
				appuser.users u 
			ON 
				c.owner_id = u.id
			WHERE 
				c.title ILIKE '%' || :key || '%'
			OR 
				:key = ANY(c.tags)
			GROUP BY 
				c.id, u.name, u.id
			ORDER BY 
				id DESC
			LIMIT 
				:length OFFSET :start`
	ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)
	defer cancel()

	args := map[string]interface{}{
		"length": filter.limit(),
		"start":  filter.start(),
		"key":    key,
	}

	rows, err := m.DB.NamedQueryContext(ctx, query, args)
	if err != nil {
		return nil, nil, err
	}
	defer rows.Close();

	courses := []*CourseBrief{}
	for rows.Next() { 
		var course CourseBrief
		err := rows.StructScan(&course)
		if err != nil {
			return nil, nil, err
		}
		courses = append(courses, &course)
	}
	if err = rows.Err(); err != nil {
		return nil, nil, err
	}

	totalRecords := 0
	if len(courses) > 0 {
		totalRecords = courses[0].Total
	}

	pageMeta := filter.calculateMetadata(totalRecords)
	return courses, pageMeta, nil
}

// 获取课程详情,包含视频
// 视频
type VideoItem struct { 
	ID 		 int64  `json:"id"`
	Index    int 	`json:"index"`
	Title    string `json:"title"`
	Duration int 	`json:"duration"`
}
// 章节
type ChapterItem struct { 
	ID 		 int64  	`json:"id"`
	Index    int 		`json:"index"`
	Title    string 	`json:"title"`
	Videos []VideoItem  `json:"videos"`
}
// 课程作者或讲师介绍
type CourseAuthor struct { 
	ID 		 		 int64  `json:"id"`
	Name    		 string `json:"name"`
	Introduction 	 string `json:"introduction"`
	SelfApplyedTitle string `json:"self_apply"`
	LearnMarkTitle   string `json:"learn_mark"`
	SellMarkTitle 	 string `json:"sell_mark"`
	LevelMarkTitle 	 string `json:"level_mark"`
}
// 课程评价概要
type CourseReviewSummary struct {
	TotalCount   int         `json:"total_count"`
	AvgRating    null.Float  `json:"avg_rating"`
	RatingCounts map[int]int `json:"rating_counts"`
}
// 课程详情
type CourseDetail struct {
	ID           	  int64   			  `json:"id" db:"id"`
	Title        	  string  			  `json:"title" db:"title"`
	Image        	  string 			  `json:"image" db:"image"`
	VideoCount   	  int64   			  `json:"video_count" db:"video_count"`
	Duration     	  int64   			  `json:"duration" db:"duration"`
	Level        	  string  			  `json:"level" db:"level"`
	Price        	  float32 			  `json:"price" db:"price"`
	Description  	  string  			  `json:"description" db:"description"`
	Skills            pq.StringArray 	  `json:"skills" db:"skills"`
	ContentsJson      string  			  `json:"-" db:"contents"`
	AuthorJson   	  string  			  `json:"-" db:"author"`
	Contents   	 	[]ChapterItem  		  `json:"contents" db:"-"`
	Author       	  CourseAuthor 		  `json:"author" db:"-"`
	StudentCount 	  null.Int  		  `json:"student_count" db:"student_count"`
	ReviewSummaryJson string 			  `json:"-" db:"review_summary"`
	ReviewSummary 	  CourseReviewSummary `json:"review_summary" db:"-"`
	IsFavorite   	  bool    			  `json:"is_favorite" db:"is_favorite"`
}

func (m *CourseModel) GetCourseDetail(courseID, userID int64) (*CourseDetail, error) {
	query := `
		SELECT
			c.id as id, c.title as title, c.image as image, COUNT(v.id) AS video_count,
			SUM(v.duration) as duration, c.level as level, c.price as price, c.description as description,
			c.skills as skills, course.get_course_structure(c.id) as contents,
			appuser.get_user_introduction(c.owner_id) as author,
			EXISTS (
				SELECT 1 FROM business.favorite_courses f
				WHERE f.user_id = $2 AND f.course_id = c.id
			) AS is_favorite,
			(
				SELECT COUNT(user_id) FROM business.orders o WHERE o.course_id = c.id
			) AS student_count,
			business.get_course_review_stats(c.id) as review_summary
		FROM 
			course.courses c
		LEFT JOIN 
			course.table_contents tc 
		ON 
			c.id = tc.course_id
		LEFT JOIN 
			course.videos v 
		ON 
			tc.id = v.parent_id
		WHERE 
			c.id = $1
		GROUP BY 
			c.id`
	ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)
	defer cancel()

	var detail CourseDetail
	err := m.DB.QueryRowxContext(ctx, query, courseID, userID).StructScan(&detail);
	if err != nil {
		return nil, err
	}

	// 解析作者信息
	err = json.Unmarshal([]byte(detail.AuthorJson), &detail.Author)
	if err != nil {
		return nil, err
	}
	detail.AuthorJson = ""

	// 解析课程结构
	err = json.Unmarshal([]byte(detail.ContentsJson), &detail.Contents)
	if err != nil {
		return nil, err
	}
	detail.ContentsJson = ""
	// 排序课程结构
	sort.Slice(detail.Contents, func(i, j int) bool {
		return detail.Contents[i].Index < detail.Contents[j].Index
	})

	// 解析课程评价概要
	err = json.Unmarshal([]byte(detail.ReviewSummaryJson), &detail.ReviewSummary)
	if err != nil {
		return nil, err
	}
	detail.ReviewSummaryJson = ""
	

	return &detail, err
}

func (m *CourseModel) GetCourseAutherId(courseId int64) (int64, error) {
	query := `SELECT owner_id FROM course.courses WHERE id = $1`

	ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)
	defer cancel()

	var authorId int64
	err := m.DB.QueryRowxContext(ctx, query, courseId).Scan(&authorId)
	if err != nil {
		if err == sql.ErrNoRows {
			return 0, nil
		} else {
			return 0, err
		}
	}
	return authorId, nil
}

// 获取课程视频文件及所在目录
func (m *CourseModel) GetCourseVideoFileAndFolder(videoId int64) (string, string, 
	error) {
	query := `
		SELECT v.video, c.location
		FROM course.videos v
		JOIN course.table_contents tc ON v.parent_id = tc.id
		JOIN course.courses c ON tc.course_id = c.id
		WHERE v.id = $1`

	ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)
	defer cancel()

	var videoFile, videoFolder string
	err := m.DB.QueryRowxContext(ctx, query, videoId).Scan(&videoFile, &videoFolder)
	if err != nil {
		return "", "", err
	}
	return videoFile, videoFolder, nil
}